import json
import requests
import pymysql

# 智慧海事-业务平台-动态执法-执法记录-巡航记录
# 发送目标请求
BASE_URL = "http://198.10.51.2:8090/api/csj-jmles/getPatrolRecode"
headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer eyJhbGciOiJIUzUxMiJ9.eyJ1c2VySWQiOjE4MzgxODEzOTI2MDAzNzEyMDEsImFjY291bnQiOiJXdEZHQ3dsNlZkVG4wOFpiNVk3czBaaVdoMC9PcHNwN251UnZzWnBhZmJRPSIsInV1aWQiOiIwZTE5ZjYxZS0yNTI3LTQ2MDUtODRjNC04ZDc2ODM5NDA2NDciLCJzdWIiOiIxODM4MTgxMzkyNjAwMzcxMjAxIiwiaWF0IjoxNzQxNjU4NTE5LCJleHAiOjE3NDE3NDQ5MTl9.vVFKl0Ch0WPKH0p1vyh1xzE0eZaVKWFZcwZonY7pcwrQfPVs_MvOPt2qtJjY8FfbhMK2FFHsEwxoouJU4iKcPA"
}

cookies = {
}

data = {
    "patrolStartTime": "2024-10-01",
    "patrolEndTime": "2024-12-31",
    "depOrg": "010202140202",
    "pageNo": 1,
    "pageSize": 1000
}

#组装请求配置
session = requests.Session()
session.headers.update(headers)


response_lo = session.post(BASE_URL,json=data,headers=headers, cookies=cookies)

#响应回来的参数
strData = json.loads(response_lo.text)
strRecordsData = strData['data']['rows'] #获取到具体数据的位置

if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'ywtg_xunhangjiancha_question_26_29'

    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strRecordsData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())

                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))

                # 构建插入语句，插入业务主表
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 插入语句
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))

        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()

print('插入完成')

